﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Proc_OrderCorrectNotices')
BEGIN
  DROP  Procedure  Proc_OrderCorrectNotices
END
GO
CREATE Procedure Proc_OrderCorrectNotices
    @PageIndex int,
	@PageSize int,
	@EnterpriseName varchar(100),
	@LawOrgan       varchar(50),
	@StartInspectionTime  datetime,
	@EndInspectionTime    datetime,
	@CorrectType    varchar(15)
AS
DECLARE @SQL nvarchar(1000)
DECLARE @RecordCount int,@P1 INT
DECLARE @Where VARCHAR(200)
IF @PageIndex <1 SET @PageIndex = 1
  SET @PageIndex = (@PageIndex - 1) * @PageSize + 1
  SET @Where = ''
IF ((@EnterpriseName IS NOT NULL) AND (RTRIM(LTRIM(@EnterpriseName))<>''))
BEGIN
  SET @Where =@Where + ' EnterpriseName LIKE ''%'+ @EnterpriseName + '%'''
END
IF ((@LawOrgan IS NOT NULL) AND (RTRIM(LTRIM(@LawOrgan))<>''))
BEGIN
  SET @Where =@Where + ' AND LawOrgan= ''' + @LawOrgan + ''''
END
IF (@StartInspectionTime IS NOT NULL)
BEGIN 
  SET @Where =@Where + ' AND InspectionDate>= ''' + Convert(varchar(10), @StartInspectionTime,120) + ''''
END
IF (@EndInspectionTime IS NOT NULL)
BEGIN
  SET @Where =@Where + ' AND InspectionDate<= ''' +  Convert(varchar(10), @EndInspectionTime,120) + ''''
END
IF((@CorrectType IS NOT NULL) AND (@CorrectType<>''))
BEGIN
  SET @Where =@Where + ' AND CorrectType= ''' + @CorrectType + ''''
END 
IF(LEFT(@Where,4)=' AND')
BEGIN
  SET @Where = RIGHT(@Where,Len(@Where)-4)
END
SET @SQL = N'SELECT * FROM OrderCorrectNotice_View ORDER BY RecordID DESC'
IF (LEN(@Where)>0)
BEGIN
  SET @SQL = 'SELECT * FROM OrderCorrectNotice_View WHERE ' + @Where + ' ORDER BY RecordID DESC'
END
EXEC sp_cursoropen @P1 OUTPUT, @SQL, @scrollopt = 1, @ccopt = 335873, @rowcount = @RecordCount OUTPUT 
IF (@P1 != 0)
BEGIN
  EXEC sp_cursorfetch @P1, 16, @PageIndex, @PageSize 
  EXEC sp_cursorclose @P1
END
SELECT @RecordCount
RETURN @@ERROR
GO



